﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Text.RegularExpressions;

namespace Backend
{
    /* Database Class 
     * This class will handle the SQL connection and
     * contain all the methods to run SQL queries. */
    class DBConnect
    {
        private MySqlConnection sqlConnection;
        private string server, username, password, database;

        public DBConnect()
        {
            Connect();
        }

        // Configure the connections information
        private void Connect()
        {
            server = "remote-mysql3.servage.net"; // remote mysql server hosted by Servage.net
            username = "scooterland";
            password = "EKr6uysNfbJjs5Gv";
            database = "scooterland";

            sqlConnection = new MySqlConnection("user id=" + username + ";" +
                                                "password=" + password + ";" +
                                                "server=" + server + ";" +
                                                "database=" + database + ";" +
                                                "connection timeout=15"); // timeout should be longer for remote servers
        }

        // Open the MySQL connection
        private bool OpenConnection()
        {
            try
            {
                sqlConnection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("Cannot connect to server. Contact administrator");
                        break;
                    case 1045:
                        Console.WriteLine("Invalid username/password, please try again");
                        break;
                    default:
                        Console.WriteLine("Connection to database server failed. Contact administrator");
                        break;
                }
                return false;
            }
        }

        // Close the MySQL Connection
        private bool CloseConnection()
        {
            try
            {
                sqlConnection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        // Test if the connection is working
        public bool TestConnection()
        {
            if (this.OpenConnection() == true)
            {
                return true;
            }
            else
                return false;
        }

        // Run a MySQL query with no return
        public void RunQuery(string query)
        {
            if (this.OpenConnection() == true)
            {
                MySqlCommand myCommand = new MySqlCommand(query, sqlConnection);
                myCommand.ExecuteNonQuery();
                this.CloseConnection();
            }
            else
                Console.Write("sql fejl");
            //                MessageBox.Show("Failed to run SQL query. Contact administrator");
        }

        // Run a MySQL query with one return
        public string RunQueryReturn(string query)
        {
            string queryReturn = "";

            if (this.OpenConnection() == true)
            {
                MySqlCommand myCommand = new MySqlCommand(query, sqlConnection);

                if (myCommand.ExecuteScalar() != null)
                    queryReturn = myCommand.ExecuteScalar().ToString();
                else
                    queryReturn = "0";

                this.CloseConnection();

                return queryReturn;
            }
            else
            {
                //                MessageBox.Show("Failed to run SQL query. Contact administrator");
                return queryReturn;
            }
        }

        public DataTable ReturnDataTable(string table, string where = "")
        {
            DataTable myTable = new DataTable(table);

            string strCountColumns = RunQueryReturn("SELECT COUNT(information_schema.columns.column_name) FROM information_schema.columns WHERE information_schema.columns.table_schema = '" + database + "' AND information_schema.columns.table_name = '" + table + "'");
            int numColumns = Convert.ToInt32(strCountColumns);

            if (this.OpenConnection() == true)
            {
                MySqlCommand columnCommand = new MySqlCommand("DESCRIBE " + table, sqlConnection);
                MySqlDataReader columnReader = columnCommand.ExecuteReader();

                string[] columnName = new string[numColumns];

                int thisColumn = -1;
                while (columnReader.Read())
                {
                    thisColumn++;
                    columnName[thisColumn] = "" + columnReader["field"] + "";
                    myTable.Columns.Add("" + columnReader["field"] + "");
                }

                this.CloseConnection();

                if (this.OpenConnection() == true)
                {
                    MySqlCommand rowCommand = new MySqlCommand("SELECT * FROM " + table + where, sqlConnection);
                    MySqlDataReader rowReader = rowCommand.ExecuteReader();

                    while (rowReader.Read())
                    {
                        object[] thisRow = new object[numColumns];

                        for (int i = 0; i <= thisColumn; i++)
                        {
                            if (i == thisColumn)
                                thisRow[i] = rowReader["" + columnName[i] + ""];
                            else
                                thisRow[i] = rowReader["" + columnName[i] + ""] + ",";
                        }

                        string newRow = string.Join("", thisRow);
                        myTable.Rows.Add(newRow.Split(','));
                    }
                }

                this.CloseConnection();

                return myTable;
            }
            else
                return myTable;
        }

        public DataTable BetterDataTable(string query, int numOutput)
        {
            DataTable myTable = new DataTable("myTable");

            if (this.OpenConnection() == true)
            {
                MySqlCommand myCommand = new MySqlCommand(query, sqlConnection);
                MySqlDataReader myReader = myCommand.ExecuteReader();
                
                int newOutput = numOutput - 1;

                for (int i = 0; i <= newOutput; i++)
                    myTable.Columns.Add(i.ToString());

                while (myReader.Read())
                {
                    object[] myRow = new object[numOutput];

                    for (int i = 0; i <= newOutput; i++)
                    {
                        if (i == newOutput)
                            myRow[i] = myReader[i];
                        else
                            myRow[i] = myReader[i] + "|";
                    }

                    string newRow = string.Join("", myRow);
                    myTable.Rows.Add(newRow.Split('|'));
                }

                this.CloseConnection();
            }

            return myTable;
        }

        public string[] ReturnArray(string query, int numOutput)
        {
            string[] returnArray;

            if (this.OpenConnection() == true)
            {
                MySqlCommand myCommand = new MySqlCommand(query, sqlConnection);
                MySqlDataReader myReader = myCommand.ExecuteReader();

                returnArray = new string[numOutput];

                int newOutput = numOutput - 1;

                while (myReader.Read())
                {
                    for (int i = 0; i <= newOutput; i++)
                        returnArray[i] = myReader[i].ToString();
                }

                this.CloseConnection();
            }
            else
                returnArray = new string[0];

            return returnArray;
        }
    }
}